package com.cx.service.admin.basic.impl;

import java.io.InputStream;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.cx.constant.BasicConstants;
import org.cx.constant.UploadCodeType;
import org.cx.entity.PageData;
import org.cx.util.AssertUtil;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.cx.dao.impl.ChainJKTDaoSupport;
import com.cx.service.RedisService;
import com.cx.service.admin.basic.UploadManager;


@Service("uploadService")
public class UploadService implements UploadManager {

    private Sheet sheet;
    private Row row;

    @Resource(name = "chainJKTDaoSupport")
    private ChainJKTDaoSupport chainJKTDao;
    
    @Resource
    private RedisService redisService;


    
    /**
     * 读取Excel数据内容
     * 
     * @param wb  excel表
     * @param beginIndex  从第几行开始
     * @param columns  数据库表字段信息
     * @param commData 系统赋予值
     * 
     * @return Map 包含单元格数据内容的Map对象
     * @author zengwendong
     */
    public List<PageData> getExcelPageData(Workbook wb,List<PageData> columns,PageData commData) throws Exception{
    	List<PageData> list = new ArrayList<PageData>();
        if(wb==null){
            throw new Exception("Workbook对象为空！");
        }
        
        //获取第一行内容
        String[] oneColumns = readExcelTitle(wb,1);
        //获取第二行内容
        String[] twoColumns = readExcelTitle(wb,2);
        //不能为空字段
        String[] notNullColumns = null;
        if(oneColumns[2] != null && !"".equals(oneColumns[2])){
        	notNullColumns = oneColumns[2].replace("必填字段：", "").split(",");
        }
        //校验是否正确传参字段
        String[] rightColumns = null;
        if(oneColumns[4] != null && !"".equals(oneColumns[4])){
        	rightColumns = oneColumns[4].split(",");
        }

        sheet = wb.getSheetAt(0);
        //获取需要系统自动填充的字段(当前操作人信息)
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        AssertUtil.isTrue( rowNum >= 4, UploadCodeType.LIST_NOT_FOUND.getMessage());

        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = 4; i <= rowNum; i++) {
        	Row row = sheet.getRow(i);
        	PageData tabPd = new PageData();
        	PageData pd = new PageData();
        	
        	//系统赋值
        	Iterator entries = commData.entrySet().iterator();
        	while (entries.hasNext()) {
        	  Map.Entry entry = (Map.Entry) entries.next();
        	  pd.put(entry.getKey(),entry.getValue() );
        	}
        	
        	for(int j = 0; j < twoColumns.length; j++){
        		//值
        		String val = cellValue(row.getCell(j));
        		String name = twoColumns[j];
        		Integer tempi = i+1;
        		//判断字符长度是否过长。
        		columns.stream().forEach((cl -> {
        			if(name.equals(cl.get("name"))){
        				BigInteger bi = (BigInteger)cl.get("length");
        				AssertUtil.isTrue(!"varchar".equals(cl.get("type")) || val.length() < bi.intValue(), "第"+tempi+"行，字段："+cl+"--值："+val+"--"+UploadCodeType.STRING_LENGTH_TOO_LONG.getMessage());
        			}
        		}));
        		
        		//判断必填项是否有空
        		if(notNullColumns != null){
        			Arrays.asList(notNullColumns).stream().forEach((cl -> {
            			if(name.equals(cl)){
            				AssertUtil.isTrue( val != null && !"".equals(val), "第"+tempi+"行，字段："+cl+"--值："+val+"--"+UploadCodeType.NOT_NULL.getMessage());
            			}
            		}));
        		}
        		
        		//判断字段内容是否正确
        		if(rightColumns != null){
        			Arrays.asList(rightColumns).stream().forEach((cl -> {
            			if(name.equals(cl) && val != null && !"".equals(val)){
            				String rightFlag = null;  //0是正确
            				Long topOrgId = (Long)pd.get("topOrgId");
            				String key = "RIGHT_FLAG_" + topOrgId+"_"+val;
            				rightFlag = redisService.getString(key);
            				if(rightFlag == null){
            					PageData rightPd = new PageData();
                				rightPd.put("topOrgId", topOrgId );
                				rightPd.put("id", val);
                				PageData orgPd = (PageData) chainJKTDao.findForObject("OrgMapper.selectOrgByTopOrgIdAndID", rightPd);
                				if(orgPd == null || orgPd.get("id") == null){
                					rightFlag = "1";  //1是错误
                				}else{
                					rightFlag = "0";  //1是正确
                				}
                				//存redis
                				redisService.set("RIGHT_FLAG_" + topOrgId+"_"+val, rightFlag, 5 * 30L);
            				}
            				//判断
            				AssertUtil.isTrue( "0".equals(rightFlag), "第"+tempi+"行，字段："+cl+"--值："+val+"--"+UploadCodeType.IS_EXIST.getMessage());
            			}
            		}));
        		}
        		
        		pd.put(twoColumns[j],val );
        	}
        	
        	tabPd.put("pd", pd);
        	tabPd.put("tableName",oneColumns[0]);
        	tabPd.put("updateStatue", oneColumns[3]);

        	list.add(tabPd);
        }
        return list;
    }
    
    /**
     * 读取Excel数据内容
     *
     * @return Map 包含单元格数据内容的Map对象
     * @author zengwendong
     */
    public List<List<String>> readExcelContent(Workbook wb,int beginIndex,String userColumn,PageData pageDate) throws Exception{

        if(wb==null){
            throw new Exception("Workbook对象为空！");
        }

        List<List<String>> listpd = new ArrayList<List<String>>();
        //  List<PageData> tableInfo = findColumnByTableName( readExcelTitle(wb,0)[0]);//根据表名获取表信息
        //   PageData pd = getColumnType(tableInfo);//获取字段类型键值对,如 map("id","Integer")
        String[] titles =  readExcelTitle(wb,1);
        sheet = wb.getSheetAt(0);
        //获取需要系统自动填充的字段(当前操作人信息)
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(beginIndex);
        int colNum = titles.length;
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = beginIndex; i <= rowNum; i++) {
            List pd = new ArrayList();
            //将需要系统自动赋值的字段先填入pd
            if(userColumn!=""){
                String[] userInfo = userColumn.split(",");
                for(String info:userInfo){
                    pd.add(pageDate.get(info));
                }
            }
            row = sheet.getRow(i);
            if(!isRowEmpty(row)){
                continue;
            }
            int j = 0;
            while (j < colNum) {
                pd.add(cellValue(row.getCell(j)));
                j++;
            }
            listpd.add(pd);
        }
        return listpd;
    }


    /**
     * 读取Excel数据内容
     *
     * @return Map 包含单元格数据内容的Map对象
     * @author zengwendong
     */
    public List<PageData> readExcelContentPageData(Workbook wb,int beginIndex,String userColumn,PageData pageDate) throws Exception{

        if(wb==null){
            throw new Exception("Workbook对象为空！");
        }

        List<PageData> listpd = new ArrayList<PageData>();
        //  List<PageData> tableInfo = findColumnByTableName( readExcelTitle(wb,0)[0]);//根据表名获取表信息
        //   PageData pd = getColumnType(tableInfo);//获取字段类型键值对,如 map("id","Integer")
        String[] titles =  readExcelTitle(wb,1);
        sheet = wb.getSheetAt(0);
        //获取需要系统自动填充的字段(当前操作人信息)
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(beginIndex);

        int colNum = titles.length;
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = beginIndex; i <= rowNum; i++) {
            PageData pd = new PageData();
            //将需要系统自动赋值的字段先填入pd
            if(userColumn!=""){
                String[] userInfo = userColumn.split(",");
                for(String info:userInfo){
                    pd.put(info,pageDate.get(info));
                }
            }
            row = sheet.getRow(i);
            if(!isRowEmpty(row)){
                continue;
            }
            int j = 0;
            while (j < colNum) {
                pd.put(titles[j],cellValue(row.getCell(j)));
                j++;
            }
            listpd.add(pd);
        }
        return listpd;
    }

    /**
     *  判断是否为空行
     * @param row
     * @return
     */
    public  boolean isRowEmpty(Row row) {
        int firstNum =  row.getFirstCellNum();
        int lastNum =row.getLastCellNum();
        int cellNum = lastNum-firstNum;

        if(cellNum==0){
            return false;
        }

        int i=0;
        for (int c = firstNum; c < row.getLastCellNum(); c++)
        {

            Cell cell = row.getCell(c);

            if (cell == null|| StringUtils.isBlank(getValue(cell))){
                i++;
            }
            if(i==cellNum){//整行为空
                return false;
            }

        }
        return true;
    }

    @Override
    public Integer saveExcelObjectIntoTable(PageData pageData,MultipartFile file) throws  Exception{
        InputStream is = file.getInputStream();
        Workbook wb = WorkbookFactory.create(is);


        //获取第一行内容
        String[] oneColumns = readExcelTitle(wb,1);
        AssertUtil.isTrue(oneColumns!=null&&oneColumns.length>0, UploadCodeType.TABLE_NOT_FOUND.getMessage());
        //获取第二行内容
        String[] twoColumns = readExcelTitle(wb,2);
        AssertUtil.isNotNull(twoColumns!=null&&twoColumns.length>0, UploadCodeType.COLUMNS_NOT_FOUND.getMessage());

        //表名
        String tableName= oneColumns[0];
        PageData tnpd = new PageData();
        tnpd.put("tableName", tableName);
        
        //数据库字段信息
        List<PageData> columns = (List<PageData>) chainJKTDao.findForList("UploadMapper.getColumns",tnpd);
        
        //增加未传数据
        PageData commData = getCommonColumn(pageData,oneColumns[1]);

        List<PageData> list = getExcelPageData(wb, columns, commData);
        
        chainJKTDao.batchSave("UploadMapper.insertOrUpdateExceltoTable",list);
        return 1;
    }



    private PageData getCommonColumn(PageData pageData, String columnOne) {
    	String[] columns = columnOne.split(",");
        PageData user = (PageData) pageData.get("user");
        PageData pd = new PageData();
        for(String col : columns){
            switch (col) {
                case BasicConstants.UPLOADCOMMCOLUMN.SOURCE:
                	pd.put(BasicConstants.UPLOADCOMMCOLUMN.SOURCE,BasicConstants.MEMBERSOURCE.IMPORT);
                	break;
                case BasicConstants.UPLOADCOMMCOLUMN.TOPORGID:
                	pd.put(BasicConstants.UPLOADCOMMCOLUMN.TOPORGID,user.get(BasicConstants.UPLOADCOMMCOLUMN.TOPORGID));
                	break;
                case BasicConstants.UPLOADCOMMCOLUMN.ORGID:
                	pd.put(BasicConstants.UPLOADCOMMCOLUMN.ORGID,user.get(BasicConstants.UPLOADCOMMCOLUMN.ORGID));
                	break;
                case BasicConstants.UPLOADCOMMCOLUMN.CREATEBY:
                	pd.put(BasicConstants.UPLOADCOMMCOLUMN.CREATEBY,user.get("userPhone"));
                	break;
                case BasicConstants.UPLOADCOMMCOLUMN.PARENTID:
                	pd.put(BasicConstants.UPLOADCOMMCOLUMN.PARENTID,user.get(BasicConstants.UPLOADCOMMCOLUMN.ORGID));
                	break;
            }
        }
        return pd;
    }


    /**
     * 读取Excel表格表头的内容
     *
     * @return String 表头内容的数组
     * @author zengwendong
     */
    public String[] readExcelTitle(Workbook wb,int rownum) throws Exception{
        if(wb==null){
            throw new Exception("Workbook对象为空！");
        }
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(rownum);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            title[i] = row.getCell(i).getStringCellValue();
        }
        return title;
    }

    public  String cellValue (Cell cell){
        if(cell ==null){
            return "";
        }
        cell.setCellType(Cell.CELL_TYPE_STRING);
        return cell.getRichStringCellValue().getString();
    }

    /**
     *
     * 根据Cell类型设置数据
     *
     * @param cell
     * @return
     * @author zengwendong
     */
    private Object getCellFormatValue(Cell cell) {
        Object cellvalue = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC
                case Cell.CELL_TYPE_FORMULA: {
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 如果是Date类型则，转化为Data格式
                        // data格式是带时分秒的：2013-7-10 0:00:00
                        // cellvalue = cell.getDateCellValue().toLocaleString();
                        // data格式是不带带时分秒的：2013-7-10
                        Date date = cell.getDateCellValue();
                        cellvalue = date;
                    } else {// 如果是纯数字

                        // 取得当前Cell的数值
                        cellvalue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                default:// 默认的Cell值
                    cellvalue = "";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }


    private static String getValue(Cell xssfRow) {
        if (xssfRow == null) {
            return "";
        }
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            return String.valueOf((int) xssfRow.getNumericCellValue());
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }

    }

    public List<PageData>  findColumnByTableName(String tableName){
        PageData pd = new PageData();
        pd.put("tableName",pd.getString("tableName"));
        List<PageData> list = ( List<PageData>)chainJKTDao.findForList("UploadMapper.findColumnByTableName",pd);
        return list;
    }

    public String[] getTitles(List<PageData> list){

        String[] titles = {};
        int i=0;
        for(PageData pd : list){
            titles[i++]=pd.getString("FIELD");
        }
        return titles;
    }

    private PageData getColumnType(List<PageData> list){
        PageData columnType = new PageData();
        for(PageData pd : list){
            String name = (String) pd.get("FIELD");
            String type = (String) pd.get("TYPE");
            if (org.springframework.util.StringUtils.hasText(type)) {
                if (type.contains("varchar")) {
                    type = "String";
                }
                if (type.contains("int")) {
                    type = "Integer";
                }
                if (type.contains("datetime"))
                {
                    type = "Date";
                }
                if(type.contains("decimal"))
                {
                    type = "Integer";
                }
            }
            columnType.put(name,type);
        }
        return columnType;
    }

}
